package dbx;

import stdx.Utils;
import stdx.ResPool;

import java.sql.*;
import java.util.Map;
import java.util.List;
import java.io.Closeable;
import java.util.HashMap;
import java.util.ArrayList;
import java.lang.reflect.Field;

public class DBConnect implements ResPool.Resource{
	public static class ResultLoop{
		protected ResultSetMetaData meta;
		protected Map<String, Integer> idxmap;

		public void next(String[] row){
		}
		public int getColumnIndex(String name){
			name = name.toLowerCase();

			if (idxmap == null){
				int res = -1;

				idxmap = new HashMap<String, Integer>();

				try {
					int len = meta.getColumnCount();

					for (int i = 0; i < len; i++){
						String key = getName(i).toLowerCase();

						if (key.equals(name)) res = i;

						idxmap.put(key, i);
					}
				}
				catch (Exception e){
					e.printStackTrace();
				}

				return res;
			}

			Integer res = idxmap.get(name);

			if (res == null) return -1;

			return res;
		}
		public Object initObject(Object obj, String[] row){
			if (obj == null) return obj;

			Field[] fields = obj.getClass().getDeclaredFields();

			try {
				for (Field field : fields){
					int index = getColumnIndex(field.getName());

					if (index >= 0) Utils.InitFieldValue(field, obj, row[index]);
				}
			}
			catch (Exception e){
				e.printStackTrace();
			}

			return obj;
		}
		public String getName(int index) throws SQLException{
			return meta.getColumnName(index + 1);
		}
	}

	protected ResPool pool = null;
	protected Exception error = null;
	protected Connection conn = null;
	protected static Pool defaultpool = new Pool();
	protected static HashMap<String, Pool> poolmap = new HashMap<String, Pool>();

	public static class Config{
		public String url = "";
		public String user = "";
		public String driver = "";
		public String passwd = "";
	}

	public static class Pool extends ResPool {
		Config cfg = new Config();

		public DBConnect get() throws Exception{
			return (DBConnect)super.get();
		}
		public Resource create() throws Exception{
			DBConnect conn = new DBConnect();
			return conn.connect(cfg);
		}
		public boolean checkSuccess(Closeable conn){
			DBConnect tmp = (DBConnect)(conn);
			if (tmp == null) return false;
			return tmp.error == null;
		}
		public Pool init(Config cfg, int maxlen, int timeout){
			this.destroy();
			this.cfg = cfg;
			this.setLength(maxlen);
			this.setTimeout(timeout);
			return this;
		}
	}

	public void close(){
		if (pool == null){
			Utils.Close(conn);
			conn = null;
		}
		else{
			pool.release(this);
		}

		error = null;
	}
	public void setPool(ResPool pool){
		this.pool = pool;
	}
	public DBConnect connect(Config cfg) throws Exception{
		close();

		try{
			conn = Connect(cfg);
		}
		catch(Exception e){
			error = e;
			throw e;
		}

		return this;
	}
	public void commit() throws SQLException{
		try{
			conn.commit();
		}
		catch(Exception e){
			error = e;
			throw e;
		}
	}
	public void rollback() throws SQLException{
		try{
			conn.rollback();
		}
		catch(Exception e){
			error = e;
			throw e;
		}
	}
	public boolean getAutoCommit() throws SQLException{
		try{
			return conn.getAutoCommit();
		}
		catch(SQLException e){
			error = e;
			throw e;
		}
	}
	public void setAutoCommit(boolean flag) throws SQLException{
		try{
			conn.setAutoCommit(flag);
		}
		catch(SQLException e){
			error = e;
			throw e;
		}
	}
	public int execute(String sqlcmd, Object...args) throws SQLException{
		int res = 0;
		PreparedStatement stmt = null;

		error = null;

		try{
			stmt = conn.prepareStatement(sqlcmd);
			Bind(stmt, args);
			stmt.addBatch();
			stmt.execute();
			res = stmt.getUpdateCount();
		}
		catch(SQLException e){
			error = e;
			throw e;
		}
		finally{
			Utils.Close(stmt);
		}

		return res;
	}
	public int query(ResultLoop loop, String sqlcmd, Object...args) throws SQLException{
		int res = 0;
		ResultSet rs = null;
		PreparedStatement stmt = null;

		error = null;

		try{
			stmt = conn.prepareStatement(sqlcmd);
			Bind(stmt, args);
			stmt.addBatch();
			rs = stmt.executeQuery();
			loop.meta = rs.getMetaData();
			String[] row = new String[loop.meta.getColumnCount()];

			while (rs.next()){
				for (int i = 0; i < row.length; i++){
					if ((row[i] = rs.getString(i + 1)) == null) row[i] = "";
				}
				loop.next(row);
				res++;
			}
		}
		catch(SQLException e){
			error = e;
			throw e;
		}
		finally{
			Utils.Close(rs);
			Utils.Close(stmt);
		}

		return res;
	}
	public <T> T select(Class<T> clazz, String sqlcmd, Object...args) throws SQLException, InstantiationException, IllegalAccessException{
		ArrayList<T> vec = selectList(clazz, sqlcmd, args);
		return vec.isEmpty() ? null : vec.get(0);
	}
	public <T> ArrayList<T> selectList(final Class<T> clazz, String sqlcmd, Object...args) throws SQLException, InstantiationException, IllegalAccessException{
		final ArrayList<T> res = new ArrayList<T>();

		query(new ResultLoop(){
			public void next(String[] row){
				T obj = Utils.GetSimpleObject(clazz, row[0]);

				if (obj == null){
					try{
						initObject(obj = clazz.getDeclaredConstructor().newInstance(), row);
					}
					catch(Exception e){
						e.printStackTrace();
					}
				}

				res.add(obj);
			}
		}, sqlcmd, args);

		return res;
	}
	public int insert(Object record, String tabname, String exclude) throws Exception, SQLException{
		String cols = "";
		String conds = "";
		List<Object> paramlist = new ArrayList<>();
		Field[] fields = record.getClass().getDeclaredFields();

		if (Utils.IsNotEmpty(exclude)) exclude = "," + exclude + ",";

		for (Field field : fields){
			String name = field.getName();
			String ename = "," + name + ",";

			if (Utils.IsNotEmpty(exclude) && exclude.indexOf(ename) >= 0) continue;

			cols += "," + name;
			conds += ",?";

			paramlist.add(field.get(record));
		}

		if (cols.isEmpty()) Utils.Throw(Utils.PARAMERR);

		String sql = String.format("insert into %s(%s) values(%s)", tabname, cols.substring(1), conds.substring(1));

		return execute(sql, paramlist.toArray());
	}
	public int update(Object record, String tabname, String keylist, String exclude, boolean updatenull) throws Exception, SQLException{
		String cols = "";
		String conds = "1=1";
		List<Object> condlist = new ArrayList<>();
		List<Object> paramlist = new ArrayList<>();
		Field[] fields = record.getClass().getDeclaredFields();

		if (Utils.IsNotEmpty(keylist)) keylist = "," + keylist + ",";
		if (Utils.IsNotEmpty(exclude)) exclude = "," + exclude + ",";

		for (Field field : fields){
			String name = field.getName();
			String ename = "," + name + ",";

			if (Utils.IsNotEmpty(exclude) && exclude.indexOf(ename) >= 0) continue;

			if (Utils.IsNotEmpty(keylist) && keylist.indexOf(ename) >= 0){
				conds += " and " + name + "=?";
				condlist.add(field.get(record));
			}
			else{
				Object data = field.get(record);

				if (updatenull || data != null) {
					cols += "," + name + "=?";
					paramlist.add(data);
				}
			}
		}

		if (cols.isEmpty()) Utils.Throw(Utils.PARAMERR);

		paramlist.addAll(condlist);

		String sql = "update " + tabname + " set " + cols.substring(1) + " where " + conds;

		return execute(sql, paramlist.toArray());
	}
	public int replace(Object record, String tabname, String keylist, String exclude, boolean updatenull) throws Exception, SQLException{
		int res = update(record, tabname, keylist, exclude, updatenull);

		if (res == 0) res = insert(record, tabname, exclude);

		return res;
	}

	public static Pool GetPool(){
		return defaultpool;
	}
	public static Pool GetPool(String name){
		if (Utils.IsEmpty(name)) return defaultpool;

		synchronized(poolmap){
			Pool pool = poolmap.get(name);

			if (pool == null){
				pool = new Pool();
				poolmap.put(name, pool);
			}

			return pool;
		}
	}
	public static DBConnect Connect() throws Exception{
		return defaultpool.get();
	}
	public static DBConnect Connect(String name) throws Exception{
		return GetPool(name).get();
	}
	public static Connection Connect(Config cfg) throws Exception{
		String driver = cfg.driver;

		if (driver.isEmpty()){
			String name = cfg.url.toLowerCase();

			if (name.indexOf("mysql") >= 0){
				driver = "com.mysql.cj.jdbc.Driver";
			}
			else if (name.indexOf("oracle") >= 0){
				driver = "oracle.jdbc.driver.OracleDriver";
			}
			else if (name.indexOf("postgres") >= 0){
				driver = "org.postgresql.Driver";
			}
			else if (name.indexOf("sqlite") >= 0){
				driver = "org.sqlite.JDBC";
			}
			
			cfg.driver = driver;
		}

		try {
			Class.forName(driver);
		}
		catch (ClassNotFoundException e){
			if (driver.equals("com.mysql.cj.jdbc.Driver")){
				Class.forName(driver = "com.mysql.jdbc.Driver");
			}
			else{
				throw e;
			}
		}

		Connection conn = null;

		if (driver.toLowerCase().indexOf("sqlite") < 0){
			conn = DriverManager.getConnection(cfg.url, cfg.user, cfg.passwd);
		}
		else{
			conn = DriverManager.getConnection(cfg.url);
		}

		conn.setAutoCommit(true);

		return conn;
	}
	public static void Bind(PreparedStatement stmt, Object...args) throws SQLException{
		int idx = 1;

		for (Object item : args){
			if (item == null){
				stmt.setNull(idx++, Types.VARCHAR);
			}
			else if (item instanceof String){
				stmt.setString(idx++, (String)(item));
			}
			else if (item instanceof Integer){
				stmt.setInt(idx++, (Integer)(item));
			}
			else if (item instanceof Double){
				stmt.setDouble(idx++, (Double)(item));
			}
			else if (item instanceof Date){
				stmt.setDate(idx++, (Date)(item));
			}
			else if (item instanceof Long){
				stmt.setLong(idx++, (Long)(item));
			}
			else if (item instanceof Short){
				stmt.setShort(idx++, (Short)(item));
			}
			else if (item instanceof Float){
				stmt.setFloat(idx++, (Float)(item));
			}
			else if (item instanceof byte[]){
				stmt.setBytes(idx++, (byte[])(item));
			}
			else{
				stmt.setString(idx++, String.valueOf(item));
			}
		}
	}
	public static int Execute(String poolname, String sqlcmd, Object...args) throws Exception, SQLException{
		DBConnect conn = Connect(poolname);

		try{
			return conn.execute(sqlcmd, args);
		}
		finally{
			Utils.Close(conn);
		}
	}
	public static int Query(String poolname, ResultLoop loop, String sqlcmd, Object...args) throws Exception, SQLException{
		DBConnect conn = Connect(poolname);

		try{
			return conn.query(loop, sqlcmd, args);
		}
		finally{
			Utils.Close(conn);
		}
	}
	public static <T> T Select(String poolname, Class<T> clazz, String sqlcmd, Object...args) throws Exception, SQLException, InstantiationException, IllegalAccessException{
		DBConnect conn = Connect(poolname);

		try{
			return conn.select(clazz, sqlcmd, args);
		}
		finally{
			Utils.Close(conn);
		}
	}
	public static <T> ArrayList<T> SelectList(String poolname, Class<T> clazz, String sqlcmd, Object...args) throws Exception, SQLException, InstantiationException, IllegalAccessException{
		DBConnect conn = Connect(poolname);

		try{
			return conn.selectList(clazz, sqlcmd, args);
		}
		finally{
			Utils.Close(conn);
		}
	}
	public static int Insert(String poolname, Object record, String tabname, String exclude) throws Exception, SQLException{
		DBConnect conn = Connect(poolname);

		try{
			return conn.insert(record, tabname, exclude);
		}
		finally{
			Utils.Close(conn);
		}
	}
	public static int Update(String poolname, Object record, String tabname, String keylist, String exclude, boolean updatenull) throws Exception, SQLException{
		DBConnect conn = Connect(poolname);

		try{
			return conn.update(record, tabname, keylist, exclude, updatenull);
		}
		finally{
			Utils.Close(conn);
		}
	}
	public static int Replace(String poolname, Object record, String tabname, String keylist, String exclude, boolean updatenull) throws Exception, SQLException{
		DBConnect conn = Connect(poolname);

		try{
			return conn.replace(record, tabname, keylist, exclude, updatenull);
		}
		finally{
			Utils.Close(conn);
		}
	}
}